﻿Option Explicit On
Option Strict On

Imports System.Data.OleDb
''' <summary>
''' conexion ala base datos conectado
''' </summary>
''' <remarks></remarks>
Public Class BBDD
    ''' <summary>
    ''' Clase que permite conectarse a una base de datos access sin contraseña
    ''' </summary>
    ''' <remarks></remarks>
    Private _strConexion As OleDbConnection
    Private _strConsulta As String
    Private _dataAdapter As OleDbDataAdapter
    Private _lector As OleDbDataReader


    Public Sub New()
        'Cadena con los datos de conexión
        Dim cadenaConexion As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\BD_Cita2002.mdb;"

        'Conexión
        Try
            _strConexion = New OleDbConnection(cadenaConexion)
        Catch e As Exception
            MessageBox.Show("Error al crear la conexión:" & vbCrLf & e.Message)
            Exit Sub
        End Try
    End Sub

    Public Sub New(ByVal dbNombre As String)
        'Cadena con los datos de conexión
        Dim cadenaConexion As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\" & dbNombre & ";"
        'Dim cadenaConexion As String = "Provider= Microsoft.jet.OLEDB.12.0; Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & dbNombre
        'Conexión
        Try
            _strConexion = New OleDbConnection(cadenaConexion)
        Catch e As Exception
            MessageBox.Show("Error al crear la conexión:" & vbCrLf & e.Message)
            Exit Sub
        End Try
    End Sub

    Public Sub Conectar()
        'Abre la conexión
        _strConexion.Open()
    End Sub

    Public Sub Desconectar()
        'Cierra la Conexión
        _strConexion.Close()
    End Sub

#Region "SELECTS"

    Public Sub EjecutarComandoSelect(ByVal sql As String)
        Dim command As OleDbCommand
        'Cadena de la consulta
        _strConsulta = sql

        'Llenamos el _dataSet con los datos de la tabla que los tiene _dataAdapter
        Try
            Me.Conectar()

            command = New OleDbCommand(sql, _strConexion)

            _lector = command.ExecuteReader()

        Catch ex As Exception
            MessageBox.Show("Error en Fill:" & vbCrLf & ex.Message)
            Me.Desconectar()
        End Try


    End Sub
#Region "no valido"
    'Public Sub EjecutarComandoSelect(ByVal tabla As String, ByVal camposTabla() As String)
    '    'TODO: Select de x campos de la tabla
    'End Sub

    'Public Sub EjecutarComandoSelect(ByVal tabla As String, ByVal camposTabla() As String, ByVal where As String)
    '    'TODO: Select de x campos de la tabla que cumplan la condición
    'End Sub
#End Region
  
#End Region

#Region "INSERTS"
    Public Function EjecutarComandoInsert(ByVal sql As String) As Boolean
        Dim ok As Boolean = True

        Dim command As New OleDbCommand
        command.CommandType = CommandType.Text

        Dim sqlInsertar As String = sql

        command.CommandText = sqlInsertar

        Try
            command.Connection = _strConexion

            Me.Conectar()

            command.ExecuteNonQuery()

            Me.Desconectar()

        Catch ex As Exception
            MsgBox(ex.Message)
            Me.Desconectar()
            ok = False
        End Try

        Return ok
    End Function

#Region "no valido"
    'Public Sub EjecutarComandoInsert(ByVal tabla As String, ByVal camposTabla() As String, ByVal datos() As String, ByVal where As String)
    '    'TODO: Inserta en los campos de la tabla los datos cuando se cumple la condición
    'End Sub

    ' ''' <summary>
    ' ''' inserta en BD un empleado 
    ' ''' </summary>
    ' ''' <param name="dniEmpleado">texto tamaño 12 obligatorio</param>
    ' ''' <param name="nombreEmpleado">texto tamaño 50 obligatorio</param>
    ' ''' <param name="primerApellidoEmpleado">texto tamaño 50 obligatorio</param>
    ' ''' <param name="segundoApellidoEmpleado">texto tamaño 50 obligatorio</param>
    ' ''' <param name="telefono">texto tamaño 9 obligatorio</param>
    ' ''' <returns>inserción correta = true</returns>
    ' ''' <remarks></remarks>
    'Public Function InsertarEmpleado(ByVal dniEmpleado As String, ByVal nombreEmpleado As String, ByVal primerApellidoEmpleado As String, ByVal segundoApellidoEmpleado As String, ByVal telefono As String) As Boolean

    '    Dim ok As Boolean = True

    '    Dim command As New OleDbCommand
    '    command.CommandType = CommandType.Text

    '    Dim sqlInsertar As String = "INSERT INTO empleados (dniEmpleado,nombreEmpleado,primerApellidoEmpleado,segundoApellidoEmpleado,telefono) VALUES ('" _
    '    & dniEmpleado & "','" & nombreEmpleado & "','" & primerApellidoEmpleado & "','" & segundoApellidoEmpleado & "','" & telefono & "');"

    '    command.CommandText = sqlInsertar

    '    Try
    '        command.Connection = _strConexion

    '        Me.Conectar()

    '        command.ExecuteNonQuery()

    '        Me.Desconectar()

    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '        Me.Desconectar()
    '        ok = False
    '    End Try

    '    Return ok
    'End Function
    ' ''' <summary>
    ' ''' inserta en BD un empleado
    ' ''' </summary>
    ' ''' <param name="idCliente">número entero largo</param>
    ' ''' <param name="horaEntrada">hora formato "20:30"</param>
    ' ''' <param name="fechaEntrada">fecha formato "27/01/2012"</param>
    ' ''' <param name="idEmpleado">número entero largo</param>
    ' ''' <param name="idTratamiento">número entero largo</param>
    ' ''' <returns>inserción correta = true </returns>
    ' ''' <remarks></remarks>
    'Public Function InsertarCita(ByVal idCliente As String, ByVal horaEntrada As String, ByVal fechaEntrada As Date, ByVal idEmpleado As String, ByVal idTratamiento As String) As Boolean

    '    Dim ok As Boolean = True

    '    Dim command As New OleDbCommand
    '    command.CommandType = CommandType.Text

    '    Dim sqlInsertar As String = "INSERT INTO Citas (idCliente,horaEntrada,fecha,idEmpleado,idTratamiento) VALUES ('" _
    '    & idCliente & "','" & horaEntrada & "','" & fechaEntrada & "','" & idEmpleado & "','" & idTratamiento & "');"

    '    command.CommandText = sqlInsertar

    '    Try
    '        command.Connection = _strConexion

    '        Me.Conectar()

    '        command.ExecuteNonQuery()

    '        Me.Desconectar()

    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '        Me.Desconectar()
    '        ok = False
    '    End Try

    '    Return ok
    'End Function
    ' ''' <summary>
    ' ''' inserta en BD un tratamientos
    ' ''' </summary>
    ' ''' <param name="nombre">texto tamaño 50</param>
    ' ''' <param name="descripcion">texto tamaño 255</param>
    ' ''' <param name="tiempoEstimado">número entero</param>
    ' ''' <param name="precio">número decimal</param>
    ' ''' <returns>inserción correta = true</returns>
    ' ''' <remarks></remarks>
    'Public Function InsertarTratamiento(ByVal nombre As String, ByVal descripcion As String, ByVal tiempoEstimado As Integer, ByVal precio As Double) As Boolean

    '    Dim ok As Boolean = True

    '    Dim command As New OleDbCommand
    '    command.CommandType = CommandType.Text

    '    Dim sqlInsertar As String = "INSERT INTO tratamientos (nombre,descripcion,tiempoEstimado,precio) VALUES ('" _
    '    & nombre & "','" & descripcion & "','" & tiempoEstimado & "','" & precio & "');"

    '    command.CommandText = sqlInsertar

    '    Try
    '        command.Connection = _strConexion

    '        Me.Conectar()

    '        command.ExecuteNonQuery()

    '        Me.Desconectar()

    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '        Me.Desconectar()
    '        ok = False
    '    End Try

    '    Return ok
    'End Function

    ' ''' <summary>
    ' ''' inserta en BD un tratamientos
    ' ''' </summary>
    ' ''' <param name="nombre">texto tamaño 50</param>
    ' ''' <param name="primerApellido">texto tamaño 50</param>
    ' ''' <param name="segundoApellido">texto tamaño 50</param>
    ' ''' <param name="telefono">texto tamaño 9</param>
    ' ''' <param name="codPostal">texto tamaño 5</param>
    ' ''' <param name="fechaNacimiento">fecha formato(27/01/2012)</param>
    ' ''' <param name="email">texto tamaño 50</param>
    ' ''' <returns>inserción correta = true</returns>
    ' ''' <remarks></remarks>
    'Public Function InsertarCliente(ByVal nombre As String, ByVal primerApellido As String, ByVal segundoApellido As String, ByVal telefono As String, ByVal codPostal As String, ByVal fechaNacimiento As Date, ByVal email As String) As Boolean

    '    Dim ok As Boolean = True

    '    Dim command As New OleDbCommand
    '    command.CommandType = CommandType.Text

    '    Dim sqlInsertar As String = "INSERT INTO clientes (nombre,primerApellido,segundoApellido,telefono,codPostal,fechaNacimiento,email) VALUES ('" _
    '    & nombre & "','" & primerApellido & "','" & segundoApellido & "','" & telefono & "','" & codPostal & "','" & fechaNacimiento & "','" & email & "');"

    '    command.CommandText = sqlInsertar

    '    Try
    '        command.Connection = _strConexion

    '        Me.Conectar()

    '        command.ExecuteNonQuery()

    '        Me.Desconectar()

    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '        Me.Desconectar()
    '        ok = False
    '    End Try

    '    Return ok
    'End Function
    ' ''' <summary>
    ' ''' inserta en BD una factura 
    ' ''' </summary>
    ' ''' <param name="idCita">número entero largo</param>
    ' ''' <param name="fecha">fecha general formato(27/01/2012 20:12:30</param>
    ' ''' <param name="total">número decimal</param>
    ' ''' <returns>inserción correta = true</returns>
    ' ''' <remarks></remarks>
    'Public Function InsertarFactura(ByVal idCita As String, ByVal fecha As Date, ByVal total As Double) As Boolean

    '    Dim ok As Boolean = True

    '    Dim command As New OleDbCommand
    '    command.CommandType = CommandType.Text

    '    Dim sqlInsertar As String = "INSERT INTO facturas (idCita,fecha,total) VALUES ('" _
    '    & idCita & "','" & fecha & "','" & total & "');"

    '    command.CommandText = sqlInsertar

    '    Try
    '        command.Connection = _strConexion

    '        Me.Conectar()

    '        command.ExecuteNonQuery()

    '        Me.Desconectar()

    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '        Me.Desconectar()
    '        ok = False
    '    End Try

    '    Return ok
    'End Function




    'Public Function InsertarLineasFactura(ByVal idFactura As String, ByVal idTratamiento As String, ByVal observaciones As String) As Boolean

    '    Dim ok As Boolean = True

    '    Dim command As New OleDbCommand
    '    command.CommandType = CommandType.Text

    '    Dim sqlInsertar As String = "INSERT INTO LineaFacturas (idFactura,idTratamiento,observaciones) VALUES ('" _
    '    & idFactura & "','" & idTratamiento & "','" & observaciones & "');"

    '    command.CommandText = sqlInsertar

    '    Try
    '        command.Connection = _strConexion

    '        Me.Conectar()

    '        command.ExecuteNonQuery()

    '        Me.Desconectar()

    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '        Me.Desconectar()
    '        ok = False
    '    End Try

    '    Return ok
    'End Function
#End Region
    
#End Region

#Region "UPDATES"

#Region "no valido"
    'Public Sub EjecutarComandoUpdate(ByVal tabla As String, ByVal camposTabla() As String, ByVal datos() As String, ByVal where As String)
    '    'TODO: Actualiza todos los campos de la tabla seleccionados(camposTabla()) con los datos nuevos(datos()) cuando se cumpla la condición (where)
    'End Sub
#End Region
    Public Function EjecutarComandoUpdate(ByVal SQL As String) As Boolean
        Dim ok As Boolean = True

        Dim command As New OleDbCommand
        command.CommandType = CommandType.Text

        Dim sqlInsertar As String = SQL

        command.CommandText = sqlInsertar

        Try
            command.Connection = _strConexion

            Me.Conectar()

            command.ExecuteNonQuery()

            Me.Desconectar()

        Catch ex As Exception
            MsgBox(ex.Message)
            Me.Desconectar()
            ok = False
        End Try

        Return ok
    End Function
#End Region

#Region "DELETES"
    Public Function EjecutarComandoDelete(ByVal sql As String) As Boolean
        Dim ok As Boolean = True

        Dim command As New OleDbCommand
        command.CommandType = CommandType.Text

        Dim sqlInsertar As String = sql

        command.CommandText = sqlInsertar

        Try
            command.Connection = _strConexion

            Me.Conectar()

            command.ExecuteNonQuery()

            Me.Desconectar()

        Catch ex As Exception
            MsgBox(ex.Message)
            Me.Desconectar()
            ok = False
        End Try

        Return ok
    End Function
#End Region

    Public Sub Dispose()

    End Sub

    Public ReadOnly Property lector As OleDbDataReader
        Get
            Return _lector
        End Get
    End Property

End Class
